package grsu.epam.airline.DAOImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import grsu.epam.airline.DAO.IEmployeeDAO;
import grsu.epam.airline.model.Employee;
import grsu.epam.airline.model.Speciality;

public class EmployeeDAOImpl implements IEmployeeDAO {
	private static final String ADD_EMPLOYEE = "insert into employees (name,surname,speciality_id,flightcrew_id) values (?,?,?,0)";
	private static final String UPDATE_EMPLOYEE = "update employees set name=?,surname=?,speciality_id=? where employee_id=?";
	private static final String GET_EMPLOYEE_BY_ID_QUERY = "select e.employee_id, e.name, e.surname,s.speciality_name,s.speciality_id from employees e inner join speciality s on e.speciality_id=s.speciality_id where e.employee_id=?";
	private static final String GET__EMPLOYEES_BY_FILGHTCREW_QUERY = "select e.employee_id, e.name, e.surname,s.speciality_name,s.speciality_id from employees e inner join speciality s on e.speciality_id=s.speciality_id where e.flightcrew_id=?";
	private static final String GET__EMPLOYEES_BY_SPECIALITY_QUERY = "select e.employee_id, e.name, e.surname,s.speciality_name,s.speciality_id from employees e inner join speciality s on e.speciality_id=s.speciality_id where e.speciality_id=? and e.flightcrew_id=0";
	private static final String GET_ALL_EMPLOYEES_QUERY = "select e.employee_id, e.name, e.surname,s.speciality_name,s.speciality_id from employees e inner join speciality s on e.speciality_id=s.speciality_id";
	private static final String DELETE_EMPLOYEE = "DELETE FROM employees WHERE employee_id=?";
	private Connection conn;

	public EmployeeDAOImpl(Connection conn) {
		this.conn = conn;
	}

	@Override
	public void addEmployee(Employee employee) throws SQLException {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(ADD_EMPLOYEE);
			stmt.setString(1, employee.getName());
			stmt.setString(2, employee.getSurname());
			stmt.setInt(3, employee.getSpeciality().getSpecialityId());
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
	}

	@Override
	public void updateEmployee(Employee employee) throws SQLException {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(UPDATE_EMPLOYEE);
			stmt.setString(1, employee.getName());
			stmt.setString(2, employee.getSurname());
			stmt.setInt(3, employee.getSpeciality().getSpecialityId());
			stmt.setInt(4, employee.getId());
			stmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
	}

	@Override
	public List<Employee> getAllEmployee() throws SQLException {
		List<Employee> employees = new ArrayList<Employee>();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.prepareStatement(GET_ALL_EMPLOYEES_QUERY);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Speciality speciality = new Speciality(
						rs.getInt("speciality_id"),
						rs.getString("speciality_name"));
				employees
						.add(new Employee(rs.getInt("employee_id"), rs
								.getString("name"), rs.getString("surname"),
								speciality));
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return employees;
	}

	public List<Employee> getEmployeesByFlightCrew(int flightCrewId)
			throws SQLException {
		List<Employee> employees = new ArrayList<Employee>();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.prepareStatement(GET__EMPLOYEES_BY_FILGHTCREW_QUERY);
			stmt.setInt(1, flightCrewId);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Speciality speciality = new Speciality(
						rs.getInt("speciality_id"),
						rs.getString("speciality_name"));
				employees
						.add(new Employee(rs.getInt("employee_id"), rs
								.getString("name"), rs.getString("surname"),
								speciality));
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}

		return employees;
	}

	public List<Employee> getEmployeesBySpeciality(int specialityId)
			throws SQLException {
		List<Employee> employees = new ArrayList<Employee>();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.prepareStatement(GET__EMPLOYEES_BY_SPECIALITY_QUERY);
			stmt.setInt(1, specialityId);
			rs = stmt.executeQuery();

			while (rs.next()) {
				Speciality speciality = new Speciality(
						rs.getInt("speciality_id"),
						rs.getString("speciality_name"));
				employees
						.add(new Employee(rs.getInt("employee_id"), rs
								.getString("name"), rs.getString("surname"),
								speciality));
			}
		} catch (Exception e) {
			throw e;
		} finally {

			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return employees;
	}

	@Override
	public Employee getEmployeeById(int employeeId) throws SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Employee employee = null;
		try {
			stmt = conn.prepareStatement(GET_EMPLOYEE_BY_ID_QUERY);
			stmt.setInt(1, employeeId);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Speciality speciality = new Speciality(
						rs.getInt("speciality_id"),
						rs.getString("speciality_name"));
				employee = new Employee(rs.getInt("employee_id"),
						rs.getString("name"), rs.getString("surname"),
						speciality);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return employee;
	}

	@Override
	public void deleteEmployee(int employeeId) throws SQLException {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(DELETE_EMPLOYEE);
			stmt.setInt(1, employeeId);
			stmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
	}

	@Override
	public Connection getConnection() {
		return this.conn;
	}
}
